# 它的特点是轻量级、高效且易于使用，非常适合用于嵌入式系统、移动应用（如Android和iOS应用）、小型网站以及作为桌面应用程序的本地存储解决方案。
# 以下是 SQLite3 的一些主要特点：
# 自包含：不需要单独安装或配置服务端进程来操作数据库文件。
# 无服务器：与大多数其他SQL数据库不同，SQLite不使用客户端/服务器架构。相反，程序直接读写磁盘上的数据库文件。
# 零配置：无需设置或管理复杂的配置文件。
# 跨平台：可以在多种操作系统上运行，包括Windows、macOS、Linux等。
# 支持大部分SQL标准：尽管不如某些大型关系型数据库那样全面，但SQLite支持大量的SQL语言特性。
# 事务处理：实现了ACID（原子性、一致性、隔离性、持久性）属性，确保了数据的可靠性和完整性。
# 扩展性：虽然设计为轻量级数据库，但在合理的范围内可以处理相对较大的数据库（理论上可达TB级别）。
import sqlite3
from datetime import datetime


# 自解：
# SQLite3 是一个软件库，它实现了一个自包含、无服务器、零配置、事务性的 SQL 数据库引擎
# SQLite3 会在本地生成一个.db文件，用于存储数据。语法与MySQL类似，适用于小型项目。

def init_db(db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_name TEXT,
            project_name TEXT,
            manager TEXT,
            phone TEXT,
            is_enabled INTEGER,
            comment TEXT,
            created_at TEXT,
            product_code TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS licenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER,
            cpu_id TEXT,
            expire_at TEXT,
            salt TEXT,
            comment TEXT,
            license_key TEXT,
            created_by TEXT,
            created_at TEXT,
            start_at TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            is_admin INTEGER,
            password_hash TEXT,
            created_at TEXT
        )
    ''')
    conn.commit()
    conn.close()

def insert_license(customer_id, cpu_id,start_at,expire_at, salt,comment, license_key,created_by, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO licenses (customer_id, cpu_id, expire_at, salt, comment,license_key, created_at,created_by,start_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (customer_id, cpu_id,expire_at, salt, comment,license_key, datetime.now().strftime('%Y-%m-%d %H:%M:%S'),created_by,start_at))
    conn.commit()
    inserted_id = cursor.lastrowid
    conn.close()
    return inserted_id

def insert_customer_info(customer_name,project_name,product_code,manager,phone,is_enabled,comment, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
    INSERT INTO customers (customer_name, project_name, manager, phone, is_enabled, comment,created_at,product_code)
                    VALUES (?,?,?,?,?,?,?,?)
    ''', (customer_name, project_name, manager, phone, is_enabled, comment, datetime.now().strftime('%Y-%m-%d %H:%M:%S'),product_code))
    conn.commit()
    conn.close()

def update_customer_info(
    customer_id,
    customer_name=None,
    project_name=None,
    product_code=None,
    manager=None,
    phone=None,
    is_enabled=None,
    comment=None,
    db_path="license_server.db"
):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 构建要更新的字段列表
    fields = []
    values = []

    if customer_name is not None:
        fields.append("customer_name = ?")
        values.append(customer_name)
    if project_name is not None:
        fields.append("project_name = ?")
        values.append(project_name)
    if product_code is not None:
        fields.append("product_code = ?")
        values.append(product_code)
    if manager is not None:
        fields.append("manager = ?")
        values.append(manager)
    if phone is not None:
        fields.append("phone = ?")
        values.append(phone)
    if is_enabled is not None:
        fields.append("is_enabled = ?")
        values.append(is_enabled)
    if comment is not None:
        fields.append("comment = ?")
        values.append(comment)

    # 如果没有字段需要更新，直接返回
    if not fields:
        conn.close()
        return False

    # 构建 SQL 语句
    sql = f'''
        UPDATE customers
        SET {', '.join(fields)}
        WHERE id = ?
    '''
    values.append(customer_id)

    try:
        cursor.execute(sql, values)
        conn.commit()
        success = cursor.rowcount > 0
    except sqlite3.Error as e:
        print(f"更新客户信息失败: {e}")
        success = False
    finally:
        conn.close()

    return success

def get_licenses_by_customer_id(customer_id, page: int = 0, page_size: int = 10, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row  # 设置 row_factory 以便按列名访问数据
    cursor = conn.cursor()

    # 获取总记录数
    cursor.execute('SELECT COUNT(*) FROM licenses WHERE customer_id = ?', (customer_id,))
    total = cursor.fetchone()[0]

    # 获取当前页的数据
    offset = page * page_size
    cursor.execute('''
        SELECT * FROM licenses
        WHERE customer_id = ?
        ORDER BY id DESC
        LIMIT ? OFFSET ?
    ''', (customer_id, page_size, offset))
    rows = cursor.fetchall()

    # 将每行数据转换为字典
    data = [{"id":c[0], "cpu_id": c[2], "expire_at": c[3],"comment":c[5],"license_key":c[6],"create_by":c[7],"create_at":c[8],"start_at":c[9]} for c in rows]

    conn.close()

    return {
        'total': total,
        'page': page,
        'page_size': page_size,
        'list': data
    }

def get_customers_paginated(page: int = 0, page_size: int = 10, customer_name: str = None, project_name: str = None, product_code: str = None, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 构建查询条件
    query_conditions = []
    query_params = []

    if customer_name and customer_name.strip():
        query_conditions.append("customer_name LIKE ?")
        query_params.append(f"%{customer_name}%")

    if project_name and project_name.strip():
        query_conditions.append("project_name LIKE ?")
        query_params.append(f"%{project_name}%")

    if product_code and product_code.strip():
        query_conditions.append("product_code LIKE ?")
        query_params.append(f"%{product_code}%")

    # 组合查询条件（使用AND连接，实现"与"的关系）
    where_clause = ""
    if query_conditions:
        where_clause = "WHERE " + " AND ".join(query_conditions)

    # 获取总记录数
    count_query = f'SELECT COUNT(*) FROM customers {where_clause}'
    cursor.execute(count_query, query_params)
    total = cursor.fetchone()[0]

    # 获取当前页的数据
    offset = page * page_size
    data_query = f'SELECT * FROM customers {where_clause} ORDER BY id DESC LIMIT ? OFFSET ?'
    cursor.execute(data_query, query_params + [page_size, offset])

    rows = cursor.fetchall()
    list = [{"id":c[0],"customer_name": c[1], "project_name": c[2], "manager": c[3], "phone": c[4], "is_enabled": c[5],
               "comment": c[6], "create_at": c[7],"product_code":c[8]} for c in rows]
    conn.close()

    return {
        'total': total,
        'page': page,
        'page_size': page_size,
        'list': list
    }

def insert_user(username, password_hash, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO users (username, password_hash, created_at,is_admin)
        VALUES (?, ?, ?, ?)
    ''', (username, password_hash, datetime.now().strftime('%Y-%m-%d %H:%M:%S'),0))
    conn.commit()
    conn.close()

def get_user_by_username(username, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users WHERE username = ?', (username,))
    row = cursor.fetchone()
    conn.close()
    return row

def get_customer_by_id(id, db_path="license_server.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
    SELECT * FROM customers WHERE id = ?''', (id,))
    row = cursor.fetchone()
    conn.close()
    return row